﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PerformanceTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var dbContext = new TestDbContext();


            Debug.WriteLine("get patient id list : " + DateTime.Now);

            var studyPatientList = dbContext.TFielddatas.Select(p => p.StudyPatientId).Distinct().Take(5).ToList();

            #region 在循环中进行SQL查询
            Debug.WriteLine("for loop : " + DateTime.Now);
            //bad 会生成多条sql语句，影响性能
            var amount = 0;
            foreach (var patientId in studyPatientList)
            {
                var patientDataCount = dbContext.TFielddatas.Count(f => f.StudyPatientId == patientId);
                amount += patientDataCount;
            }

            //改善方案，如果studyPatientList数据量较少，可以直接先tolist，然后进行contain查询。
            //         如果studyPatientList数据量较多，考虑使用IQueryable与IQueryable对象进行join查询
            var total = dbContext.TFielddatas.Count(f => studyPatientList.Contains(f.StudyPatientId));

            var patientIdList = dbContext.TFielddatas.Select(p => p.StudyPatientId).Distinct().Take(500);
            var totalCount =
                (from p in dbContext.TFielddatas
                 join s in patientIdList
                 on p.StudyPatientId equals s
                 select p.Id).Count();
            #endregion

            /*
            #region 使用contain

            Debug.WriteLine("contain : " + DateTime.Now);

            var containPerformance = dbContext.TFielddatas.Where(p => studyPatientList.Contains(p.StudyPatientId));
            Console.WriteLine("containPerformanceCount = " + containPerformance.Count());

            #endregion

            #region IQueryable和IEnumerable进行join，会引发IQueryable对象立即查询数据库

            Debug.WriteLine("IQueryable join IEnumerable : " + DateTime.Now);

            var fieldDatas = dbContext.TFielddatas.Take(1000000);
            var joinPerformance =
                from s in studyPatientList
                join f in fieldDatas //dbContext.TFielddatas
                on s equals f.StudyPatientId
                select new { f.DfTableid };
            foreach (var tableId in joinPerformance)
            {
                Console.WriteLine("jtableId = " + tableId);
            }
            Console.WriteLine("joinPerformanceCount = " + joinPerformance.Count());

            #endregion


            #region 不当的参数会引发类型转换
            var typeCast = dbContext.TFielddatas.Count(p => p.Isdeleted == ((int)Status.Deleted).ToString() && p.StudyPatientId == 20);
            var deletedStatus = "1";
            var deletedStatusFromEnum = DbFunctions.AsNonUnicode(((int)Status.Deleted).ToString());
            var nonTypeCast = dbContext.TFielddatas.Count(p => p.Isdeleted == deletedStatus && p.StudyPatientId == 20);
            var nonTypeCast2 = dbContext.TFielddatas.Count(p => p.Isdeleted == deletedStatusFromEnum && p.StudyPatientId == 20);
            Console.WriteLine("See sql");
            #endregion

            */

            Console.ReadKey();
        }
    }

    public enum Status
    {
        Deleted = 1
    }
}
